-- AV -- Změna typu atriburu (SPOUŠTĚT)
<g>
-- AttrType use internal values from 222,0 attribute ; AttrLength use values from a223u0 attribute
-- Not support all combinations (do not use exceptions; when not supported then nothing done), tested only change from string to text or lstring to ltext
create procedure dbo.ChangeAttrType(@AttrIC bigint, @AttrU int, @NAttrType int, @NAttrLength int) as
begin
  -- Fill classname and attrname
  declare @classname nvarchar(50)
  select @classname = 'a' + cast(ic_a as nvarchar(20)) + 'u' + cast(u_a as nvarchar(10)) from a103u0 where ic_b = @AttrIC and u_b = @AttrU
  declare @attrname nvarchar(50) = 'a' + cast(@AttrIC as nvarchar(20)) + 'u' + cast(@AttrU as nvarchar(10));
  declare @attrindexed bit; -- determine if new attribute will be indexed
  declare @oattrtype int;
  select @oattrtype = a222u0, @attrindexed = case when @NAttrType in (6,8,9,13) or a2270u0 = 0 or a2271u0 = 0 or (a2270u0 is null and a2271u0 is null and @NAttrLength > 250) then 0 else 1 end  from a102u0 where ic = @AttrIC and u = @AttrU

  declare @oislangattr bit = case when @oattrtype in (2,9,13) then 1 else 0 end
  declare @nislangattr bit = case when @NAttrType in (2,9,13) then 1 else 0 end
  if (@oislangattr = @nislangattr) -- changes from ml attr to non ml attr and vice versa not supported
  begin
    declare @nattrtypename nvarchar(100) = 
	  case @NAttrType 
	    when 1 then 'nvarchar(' + cast(@NAttrLength as nvarchar(5))+ ')'
		when 2 then 'nvarchar(' + cast(@NAttrLength as nvarchar(5))+ ')'
		when 3 then 'integer'
		when 4 then 'float'
		when 5 then 'datetime'
		when 6 then 'varbinary(max)'
		when 7 then 'integer'
		when 8 then 'nvarchar(max)'
		when 9 then 'nvarchar(max)'
		when 10 then 'datetime'
		when 11 then 'datetime'
		when 12 then 'tinyint'
		when 13 then 'varbinary(max)'
		when 14 then 'integer'
		when 15 then 'smallint'
		when 16 then 'bigint'
	  end;
    
    declare @langic bigint
	declare @langsuffix nvarchar(100)
    declare @fullattrname nvarchar(100)
	declare @attrcollation nvarchar(50)
	declare @attrindexes int = 0;

	if (@nislangattr = 1)
	begin
	  declare langs scroll cursor for select ic,'l' + cast(ic as nvarchar(30)) + 'u' + cast(u as nvarchar(20)) from a194u0
	  open langs
	  fetch first from langs into @langic,@langsuffix
	  while (@@fetch_status = 0)
	  begin
	    if @attrindexed = 1
	      set @attrindexes = @attrindexes + power(2,@langic-400);
	    set @fullattrname = @attrname + @langsuffix;
		select @attrcollation = collation from syscolumns where name = 'a230u0' + @langsuffix;

		if exists(select * from sysindexes where name = 'i_' + @fullattrname)
		  exec('drop index ' + @classname + '.i_' + @fullattrname);
		if (@attrcollation is not null) 
		  exec('alter table ' + @classname + ' alter column ' + @fullattrname + ' ' + @nattrtypename + ' collate ' + @attrcollation) else
		  exec('alter table ' + @classname + ' alter column ' + @fullattrname + ' ' + @nattrtypename);
		if (@attrindexed = 1)
		  exec('create index i_' + @fullattrname + ' on ' + @classname + '(' + @fullattrname + ')');

	    fetch next from langs into @langic,@langsuffix;
      end;
      close langs
      deallocate langs
	end else
	begin
	  set @fullattrname = @attrname;
	  set @attrindexes = @attrindexed;
	  select @attrcollation = collation from syscolumns where name = 'a229u0';

	  if exists(select * from sysindexes where name = 'i_' + @fullattrname)
		  exec('drop index ' + @classname + '.i_' + @fullattrname);
	  if (@attrcollation is not null) 
		  exec('alter table ' + @classname + ' alter column ' + @fullattrname + ' ' + @nattrtypename + ' collate ' + @attrcollation) else
		  exec('alter table ' + @classname + ' alter column ' + @fullattrname + ' ' + @nattrtypename);
	  if (@attrindexed = 1)
		  exec('create index i_' + @fullattrname + ' on ' + @classname + '(' + @fullattrname + ')');
	end;

	-- Update builder definition
	update a102u0 set a222u0 = @NAttrType, a223u0 = @NAttrLength, a2269u0 = @attrindexes where ic = @AttrIC and u = @AttrU
  end; 
end;

-- změna atributů
exec dbo.ChangeAttrType 2575988,10100,8,null -- Monitoring obchodních partnerů -- Popis

drop procedure ChangeAttrType
</g>
-- AV - Konec